﻿#encoding: utf-8
=begin

Usage:      This ruby file will update crmbe product tags in keyword field
arguments:  ruby update-tags.rb

=end

load 'rds_api.rb'
load 'crmeb_utils.rb'

def get_products_ conditions
  products = []
  inq = "select id, auto_hide, source_pid, source_mid, sales, price, keyword, store_name, store_info, stock, is_show, cate_id, sort, psort, temp_id, mer_id from crmeb.eb_store_product "
  inq += conditions.empty? ? 'where is_del = 0;' : " #{conditions};"
  res = queryRds inq
  res.each { |product| products += [product] }
  return products
end

def update_tags_for products

  idx = 0
  products.each do |product|

    keywords = {}
    tags = []
    merchant = '这是臭虫' #理论上不应该有缺少mer_id的产品
    template = '包邮规则失效'

    security = product['security']

    begin
      keywords = JSON.parse product['keyword']
    rescue => e
      #如果keyword为空解析会出错,这时候把原值传给它
      keywords = product['keyword']
    end

    #允许用户第一次输入特性标签列表
    if keywords.class == String
      tags = keywords.split(" ") #针对默认的字符串设为将头三个设为特性标签 
      tags.size.times { |idx| tags[idx] = tags[idx][0..9] } #不允许超过6个字符
      tags = tags[0..4] if product['mer_id'] > 1 #限制非自营的标签呈现数量
    end
    
    #优先使用已保留的合法数据
    if keywords.class == Hash && keywords['tags'] && keywords['tags'].class == Array
      tags = keywords['tags'] 
    end
    #根据用户提交信息更新其他标签，尚未实现
    #{"brand":"绿手指农场","supplier":"绿手指农场","producer":"珠海高栏港平沙镇/绿手指有机农业园","security":"中国有机认证","conditions":"#低温冷藏3天"}

    keywords = {} if keywords.class != Hash #如果是合法的Hash则保留之前解析出的记录,否则重构
    tags += [keywords['security']] if keywords['security'] && keywords['security'] != '-'

    #去除pid标签
    pidTag = "p##{product['id']}"
    tags -= [pidTag]

    #准备商店名称，强制更新
    merchant = merchantList[product['mer_id']]["name"]
    begin
      template = templateList[product['temp_id']]["name"]
    rescue=>e
      #会有商品未及时更新删除的邮件模板
    end

    #准备商品运费及配送时间说明，强制更新
    shipping = ''
    queryRds("select description from crmeb.eb_shipping_templates where id = #{product['temp_id']}").each do |qr| 
      shipping = qr['description']
      break
    end

    keywords.store('security',security) if security
    keywords.store('shipping',shipping) if shipping
    keywords.store('merchant',merchant) if merchant
    keywords.store('template',template) if template

    #重置排序顺序，当前公式为销量*售价，假设常用数为10000-100000，大100000的，大于部分取2的对数后乘1000, 可以再加个库存的对数
    base = product['sales']*product['price']
    exp = base > 100000 ? Math.log(base,2)*1000 : 0
    psort = base > 100000 ? (100000 + exp*1000).to_i : base.to_i

    cate_ids = []
    product['cate_id'].split(',').uniq.each { |cat| cate_ids += [cat] }
    cate_ids = cate_ids - [2]
    cate_id = cate_ids.join(',')

    tags += ['产地直发'] if product['store_name'].include? '产地直发'

    keywords.store('tags',tags.uniq) if tags

    #移除产地直发目录3及超值好物目录2，但保留标签

    #如果是代销商品，同步其所有的库存
    sync_consign_product product if product['source_pid'] > 0

    #这里先弥补一个stock缺陷，对于普通商品（type==0）已知系统取消未支付订单时，除了vsotck，实际stock未回撤。在修正这一缺陷之前，在本定时任务中强制更新
    res = queryRds "select sum(stock) as vstock from crmeb.eb_store_product_attr_value where product_id = #{product['id']} and type = 0"
    vstock = 0
    res.each do |r|
      vstock = r['vstock']
      vstock = 0 if vstock.nil? #避免不连续的产品数据
      break;
    end

    print "v" if vstock != product['stock']
    is_show = product['is_show']
    is_show = vstock > 0 ? 1 : 0 if product['auto_hide'] == 1
    is_show = 0 if (product['store_info'].include? '[ZY]') && vstock > 0 && product['is_show'] == 0 #专营商品如果有库存且被手工下架状态，则保持下架以处理小程序审核时的经营许可检查
    is_show = 0 if merchantList[product['mer_id']]['status'] == 'close'

    psort = 32767 if psort > 32767 #psort is smallint
    sql = "update crmeb.eb_store_product set keyword='#{@rds.escape keywords.to_json}', stock = #{vstock}, is_show=#{is_show}, cate_id = '#{cate_id}', psort=#{psort} where id = #{product['id']}"
    idx += 1 

    queryRds sql

    print "."

  end
  puts "\ndone. #{idx}"

end

#由于某些原因，例如微信地址格式的问题，某些用户提交订单时的地址中，cityid为零
def fix_city_id
  usqls = []
  puts "fixing city_id for address..."
  inq = "select id, uid, city from crmeb.eb_user_address where city_id = 0;"
  queryRds(inq).each do |q|
    addr_id = q['id']
    city_name = q['city']
    city_id = q['city_id']
    sql = "select city_id from crmeb.eb_system_city where name = '#{city_name}';"
    queryRds(sql).each do |u|
      city_id = u['city_id']
      break
    end
    usqls += ["update crmeb.eb_user_address set city_id = #{city_id} where id = #{addr_id};    ##{Time.now.to_s}"]
  end
  if usqls.size > 0
    append_lines_to_fix usqls, 'cityid_fix_sql.log'
    commitTrans usqls
  end
  puts "done."
end

def reset_products_ feature

  #不需要按照商家分配权重的版块,无需每个商家去循环
  if feature == 'is_benefit' || feature == 'is_hot'
    reset_mer_products_ 0, 1, feature 
    return
  end

  merIds = {}
  total_weight = 0.0
  merchantList.each do |mer_id, mer_stat|
    total_weight += mer_stat['weight']
    merIds.store(mer_id, mer_stat['weight'])
  end
  merIds.each do |mer_id, weight|
    reset_mer_products_ mer_id, weight/total_weight, feature
  end

end

def reset_mer_products_ mer_id, weight, feature

  using_merid = true
  using_merid = false if mer_id == 0 #如果mer_id为零，则不按mer_id分配

  case feature
  when 'is_new' #节气风物 首发新品
    where = "where is_show = 1 and source_pid = 0" 
    where = "where is_show = 1 and source_pid = 0 and mer_id = #{mer_id}" if using_merid
    sort='order by add_time desc'
    limit = (24*weight).to_i
  when 'is_benefit' #狗蛋福利 限时特购
    where="where is_show = 1 and ( store_name like '%特惠%' or name_prefix like '%特惠%' or store_name like '%新年福至%')" 
    where="where is_show = 1 and ( store_name like '%特惠%' or name_prefix like '%特惠%' or store_name like '%新年福至%' ) and mer_id = #{mer_id}"  if using_merid
    sort='order by stock desc'
    limit = (24*weight).to_i
  when 'is_hot' #巢中好物热门榜单 groom_list
    queryRds "update crmeb.eb_store_product set #{feature} = 0 where 1=1;"
    queryRds "update crmeb.eb_store_product set #{feature} = 1 where is_show = 1 order by psort desc limit 32;"
    return
    where = "where is_show = 1"
    where = "where is_show = 1 and mer_id = #{mer_id}" if using_merid
    where = "" #按照实际设置来
    sort='order by psort desc'
    limit = (16*weight).to_i
  when 'is_best' #超值推荐有口皆碑
    where = 'where is_show = 1'
    where = "where is_show = 1 and mer_id = #{mer_id}" if using_merid
    where = "" #按照实际设置来
    sort='order by sales desc'
    limit = (24*weight).to_i
  when 'is_good' #商品详情优品推荐 
    where = 'where is_show = 1'
    where = "where is_show = 1 and mer_id = #{mer_id}" if using_merid
    where = "" #按照实际设置来
    sort='order by psort desc'
    limit = (16*weight).to_i
  else
    return
  end

  #只按照原义设置相关标志
  if !where.to_s.empty?
    #重置
    sql = "update crmeb.eb_store_product set #{feature} = 0 where 1=1;"
    sql = "update crmeb.eb_store_product set #{feature} = 0 where 1=1 and mer_id = #{mer_id};" if using_merid
    queryRds sql
    queryRds "update crmeb.eb_store_product set #{feature} = 1 #{where} #{sort} limit #{limit*2};"
  end

  #获得已设置为当前FEATURE商品
  pids = []
  sql = "select id from crmeb.eb_store_product where is_show=1 and #{feature} = 1;"
  sql = "select id from crmeb.eb_store_product where is_show=1 and mer_id = #{mer_id} and #{feature} = 1;" if using_merid
  queryRds(sql).each { |q| pids += [q['id']] }

  #获得已设置为当前FEATURE商品的代销商品
  to_remove = []
  sql = "select id, source_pid from crmeb.eb_store_product where is_show=1 and #{feature} = 1 and source_pid > 0;"
  sql = "select id, source_pid from crmeb.eb_store_product where is_show=1 and mer_id = #{mer_id} and #{feature} = 1 and source_pid > 0;" if using_merid

  #如果代销商品的源商品也被设置为了当前FEATURE商品则将其从商品列表中除去
  queryRds(sql).each { |q| to_remove += [q['id']] if pids.include? q['source_pid'] }
  pids = pids.reverse - to_remove #当order by psort desc limit 10的时候，sort最大的行在数组的最后，所以要把他们反过来用
  plist = pids[0..limit-1].join(',')

  #将结果列表中的pid置为相关feature启用
  if using_merid
    queryRds "update crmeb.eb_store_product set #{feature} = 0 where mer_id = #{mer_id};" 
    queryRds "update crmeb.eb_store_product set #{feature} = 1 where mer_id = #{mer_id} and id in (#{plist});" if plist.size > 0
  else
    queryRds "update crmeb.eb_store_product set #{feature} = 0 where 1=1;" 
    queryRds "update crmeb.eb_store_product set #{feature} = 1 where id in (#{plist});" if plist.size > 0
  end

end

def reset_item_flags

  puts "reset item flags..."
  #每个商家设置的热门榜单is_hot，促销单品is_benefit，精品推荐is_best，首发新品is_new，优品推荐is_good不超过12及(1+12/商家数)中的最小值

  begin
    ['is_hot','is_new','is_benefit','is_best','is_good'].each do |feature|
      puts "reset #{feature} ..."
      reset_products_ feature
      puts "done"
    end
  rescue => e
    puts e
    exit
  end

end

def update_activities act_type

  usqls = []
  data_value = {}

  #准备查询产品名字以及关闭滚动新闻提示的语句
  #吐个槽，update eb_system_group_data 时候总是报没有权限错误，复制到navicat看着一模一样的就是一个行一个不行，motherfuck
  case act_type
  when :combination
    puts "update group-buy activity..."
    data_id = 191
    act_news = '开团提醒：PRODUCT_NAME 等正在拼团共购'
    inq = "select id, title as name, info as intro, image from crmeb.eb_store_combination where is_del = 0 and is_show = 1 order by add_time desc"
    data_value = {"info":{"type":"input","value":""},"url":{"type":"select","value":"/pages/activity/goods_combination/index"},"show":{"type":"radio","value":"2"},"wap_url":{"type":"select","value":"/activity/group"}}
    usql = "UPDATE crmeb.eb_system_group_data SET status = 2 WHERE id = #{data_id};" #status == 1 启用 ==2 关闭
    queryRds usql
  when :seckill
    puts "update flash-buy activity..."
    data_id = 194
    act_news = '秒杀提醒：PRODUCT_NAME 等正在限量秒杀'
    inq = "select id, title as name, info as intro, image from crmeb.eb_store_seckill where status = 1 and is_del = 0 and is_show = 1 order by add_time desc"
    data_value = {"info":{"type":"input","value":""},"url":{"type":"select","value":"/pages/activity/goods_seckill/index"},"show":{"type":"radio","value":"2"},"wap_url":{"type":"select","value":"/activity/goods_seckill"}}
    usql = "UPDATE crmeb.eb_system_group_data SET `status` = 2 WHERE id = #{data_id};"
    queryRds usql
  when :bargain
    puts "update group-bargain activity..."
    data_id = 195
    act_news = '砍价提醒：PRODUCT_NAME 等正在限量砍价'
    inq = "select id, title as name, info as intro, image from crmeb.eb_store_bargain where is_del = 0 and status = 1 order by add_time desc"
    data_value = {"info":{"type":"input","value":""},"url":{"type":"select","value":"/pages/activity/goods_bargain/index"},"show":{"type":"radio","value":"2"},"wap_url":{"type":"select","value":"/activity/bargain"}}
    usql = "UPDATE crmeb.eb_system_group_data SET `status` = 2 WHERE id = #{data_id};"
    queryRds usql
  when :last_sale
    puts "unsupported activity type"
    act_news = '成交动态：CUSTOMER_NAME 刚刚购买了PRODUCT_NAME'
    return
  when :last_comment
    return
  end

  queryRds(inq).each do |qi|
    #只需要第一个记录结果
    product_name = qi['name'].split(' ')[0].split('　')[0].split('(')[0].split('（')[0]
    intro = qi['intro']
    image = qi['image']
    data_value[:info][:value]=act_news.sub("PRODUCT_NAME",product_name);
    usql = "UPDATE crmeb.eb_system_group_data SET `status` = 1, value = '#{@rds.escape data_value.to_json}' where id = #{data_id};"
    queryRds usql
    break
  end
  puts "done."

end

#to export stocks execel and change banner for closed store
def check_store_status
  data_value = {"name":{"type":"input","value":"店铺盘点版头，修改店名启用，例如'存在小铺正在盘点|19点恢复上架，不便处请多包涵'"},"url":{"type":"input","value":"\/pages\/news_details\/index?id=2"},"pic":{"type":"upload","value":"https:\/\/oss.foodtrust.cn\/\/90631202009080935227888.jpg"},"wap_url":{"type":"input","value":"\/news_detail\/2"}}
  merchantList.each do |mer_id, mer_stat|
    usql = "UPDATE crmeb.eb_system_group_data SET `status` = 2, value = '#{@rds.escape data_value.to_json}' where mer_id = #{mer_id} and value like '%盘点%';"
    if mer_stat['status'] == 'close'
      puts "#{mer_id} is closed, export stocks"
      export_merchant_stocks mer_id  
      data_value[:name][:value] = "#{mer_stat['name']} | #{mer_stat['intro']}" #一般来说这时候分离出来的name 'XXX正在盘点'
      usql = "UPDATE crmeb.eb_system_group_data SET `status` = 1, value = '#{@rds.escape data_value.to_json}' where mer_id = #{mer_id} and value like '%盘点%';"
    end
    queryRds usql
  end
end

#to create template for new created store
def create_templates_for_new_store

  sql = "update crmeb.eb_system_store set tags='TAGS' where id=MER_ID;" #我也不知道为啥直接生产sql代码会出权限错，MD

  merchantList.each do |mer_id, state|

    tags = state['tags']
    if tags.include? 'TCT'

      store_name = state['name']
      clear_mer_templates mer_id
      copy_mer_templates 0, mer_id
      create_group_data mer_id
      create_category mer_id, store_name

      new_tags = ((tags.split('|') - ['TCT'])).join('|')
      queryRds sql.gsub('TAGS',new_tags).gsub('MER_ID',mer_id.to_s)

    end

  end

end

def regen_cate_ids
    product_ids = []
    cates_ids = []
    inq = "select id, cate_id from crmeb.eb_store_product"
    queryRds(inq).each do |q|
      product_ids += [q['id']]
      cates_ids += [ q['cate_id'] ]
    end
    product_ids.size.times do |idx|
      queryRds "delete from crmeb.eb_store_product_cate where product_id = #{product_ids[idx]}"
      cates_ids[idx].split(',').each do |cat_id|
        queryRds "insert into crmeb.eb_store_product_cate (product_id, cate_id, add_time) values (#{product_ids[idx]}, #{cat_id}, #{Time.now.to_i} );"
      end
    end
end

#同步代销商品的库存,consign_pid为代销商品id，source_pid为源商品id, is_show=1 
def sync_consign_product product

  consign_pid = product['id']
  source_pid = product['source_pid']

  #需要同步consign_ratio
  sql = "UPDATE crmeb.eb_store_product t1 join ( select consign_ratio, price, id from crmeb.eb_store_product where id = #{source_pid} limit 1) 
          as t2 on t1.id = #{consign_pid} set t1.consign_ratio = t2.consign_ratio, t1.price = t2.price"
  queryRds sql

  consign_avs = []
  queryRds("select * from crmeb.eb_store_product_attr_value where product_id = #{consign_pid} and type = 0").each { |q| consign_avs += [q] }
  source_avs = []
  queryRds("select * from crmeb.eb_store_product_attr_value where product_id = #{source_pid} and type = 0").each { |q| source_avs += [q] }

  consign_uniques = []
  consign_avs.each { |av| consign_uniques += [av['unique']] }

  #如果是多规格的商品，则应同步attr和attr_result(attr用于在后台商品编辑时获得有效的规格值来索引attr_value(或者是attr_result?)
  if consign_avs.size > 1 #TBD
    queryRds("select result from crmeb.eb_store_product_attr_result where product_id = #{source_pid} and type = 0").each do |qar|
      result = qar['result']
      queryRds "update crmeb.eb_store_product_attr_result set result = '#{@rds.escape result}' where product_id = #{consign_pid} and type = 0"
      break #result应该只有一条的
    end
    #更新attr，这里有一个bug，如果attr数量增加减少，或者名字改变则不工作
    queryRds("select attr_name, attr_values from crmeb.eb_store_product_attr where product_id = #{source_pid} and type = 0").each do |qar|
      attr_name = qar['attr_name']
      attr_values = qar['attr_values']
      queryRds "update crmeb.eb_store_product_attr set attr_values = '#{attr_values}' where product_id = #{consign_pid} and type = 0 and attr_name = '#{attr_name}'"
    end
  end

  if consign_avs.size != source_avs.size
    #如果选项数量不一致，则重置所有选项
    queryRds "delete from crmeb.eb_store_product_attr_value where product_id = #{consign_pid} and type = 0;"
    sql = "insert into
      crmeb.eb_store_product_attr_value (
        product_id, suk, stock, sales, price, image, `unique`, cost, 
        bar_code, ot_price, weight, volume, brokerage, brokerage_two, `type`, quota, quota_show)
      select
        #{consign_pid} as product_id, suk, stock, 0 as sales, price, image, concat(`unique`,'-#{source_pid}') as `unique`, cost, 
        bar_code, ot_price, weight, volume, brokerage, brokerage_two, 0 as `type`, quota, quota_show
      from
        crmeb.eb_store_product_attr_value
      where
        product_id = #{source_pid} and `type` = 0"
    queryRds sql
  else
    #如果选项数量一致，则更新代销商品选项中的相关内容
    source_avs.size.times do |idx|

      spav = source_avs[idx] #source product attribute value
      stock = spav['stock']
      cuniq = consign_avs[idx]['unique']
      sql = "update crmeb.eb_store_product_attr_value 
        set
          suk = '#{spav['suk']}',
          stock = #{spav['stock']},
          price = #{spav['price']},
          cost = #{spav['cost']},
          bar_code = '#{spav['bar_code']}',
          ot_price = #{spav['ot_price']},
          weight = #{spav['weight']},
          volume = #{spav['volume']},
          brokerage = #{spav['brokerage']},
          brokerage_two = #{spav['brokerage_two']},
          quota = #{spav['quota']},
          quota_show = #{spav['quota_show']}
        where
          `unique` = '#{cuniq}'"
      queryRds sql
    end
  end

end

=begin
    select id from crmeb.eb_store_product where price < ot_price * 0.88; # and mer_id = 1 and no_discount = 0;
    select product_id from crmeb.eb_store_product_attr_value where price < ot_price * 0.88 and type = 0 group by product_id;
    select id from crmeb.eb_store_product where id in (select product_id from crmeb.eb_store_product_attr_value where price < ot_price * 0.88 and type = 0 group by product_id);
    select id, store_name, mer_id, source_mid from crmeb.eb_store_product where id in (select id from crmeb.eb_store_product where price < ot_price * 0.88) and id not in (select product_id from crmeb.eb_store_product_attr_value where price < ot_price * 0.88 and type = 0 group by product_id);
    select id, store_name, mer_id, source_mid from crmeb.eb_store_product where id not in (select id from crmeb.eb_store_product where price < ot_price * 0.88) and id in (select product_id from crmeb.eb_store_product_attr_value where price < ot_price * 0.88 and type = 0 group by product_id);
=end

def set_no_discount
    #目前是hardcode
    sqls = []
    sqls += ['update crmeb.eb_store_product set no_discount = 1 where id in (select product_id from crmeb.eb_store_product_attr_value where price < ot_price * 0.88 and type = 0 group by product_id) and mer_id = 1;']
    sqls += ['update crmeb.eb_store_product set no_discount = 1 where price < ot_price * 0.88 and mer_id = 1;']
    sqls += ['update crmeb.eb_store_product set no_discount = 0 where id in (select product_id from crmeb.eb_store_product_attr_value where price = ot_price and type = 0 group by product_id) and mer_id = 1;']
    sqls += ['update crmeb.eb_store_product set no_discount = 0 where price = ot_price and mer_id = 1;']
    commitTrans sqls
end

#product={}
#product.store('id',2258)
#product.store('source_pid',2063)
#sync_consign_product_stock product

#reset_item_flags

puts "update stocks for closed store"
load 'update-stocks.rb'

puts "export stocks for closed store"
check_store_status
create_templates_for_new_store

puts "update tags for products..."
products = get_products_ ""
update_tags_for products

puts "update no discount for merid 1..."
set_no_discount

fix_city_id
[:combination, :seckill, :bargain].each { |act| update_activities act }

puts "update coupons due to sepearte merchant"
load "update-coupons.rb"

puts "update consigns due for all orders"
load "update-consigns.rb"

puts "update merchants statics"
load "update-merchants.rb"

puts "update search index"
load 'update-search-index.rb'

puts "update tutem database"
load 'update-tutem.rb'

puts "auto settle"
load 'settle-fc.rb'
